package com.xms.datacore;

import com.xms.core.exception.XmsException;
import com.xms.datacore.provider.IDataBase;
import com.xms.schema.core.EntityMask;
import com.xms.schema.entity.Attribute;
import com.xms.schema.entity.Entity;
import com.xms.schema.entity.RelationShip;
import com.xms.schema.extension.AttributeExt;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

/**
 * <p>
 * MetadataProviderImpl
 * </p>
 *
 * @author migo
 * @since 2021/8/31
 */

//@Service
public class MetadataProvider implements IMetadataProvider {

    private IDataBase _database;
    @Autowired
    public MetadataProvider(IDataBase myDataBase) {
        _database = myDataBase;
    }

    @Override
    public void addColumn(Attribute... attributes) throws XmsException, SQLException {
        Assert.notEmpty(Arrays.asList(attributes), "attributes must not be empty");
        if(!tableExists(attributes[0].getEntityname())){
            return;
        }
        StringBuilder sql = new StringBuilder();
        for (Attribute attr : attributes)
        {
            if (!this.columnExists(attr.getEntityname(), attr.getName()))
            {
                sql.append(String.format("ALTER TABLE %s ADD COLUMN %s %s"
                        , getSchemaTableName(attr.getEntityname()), attr.getName(), AttributeExt.getDbType(attr)));
            }
        }
        if (sql.length() > 0)
        {
            _database.execute(sql.toString());
        }
    }

    @Override
    public void alterColumn(Attribute... attributes) throws SQLException {
        if(!tableExists(attributes[0].getEntityname())){
            return;
        }
        for (Attribute attr : attributes)
        {
            if (this.columnExists(attr.getEntityname(), attr.getName()))
            {
                _database.execute(String.format("ALTER TABLE %s ALTER COLUMN %s TYPE %s"
                        , getSchemaTableName(attr.getEntityname()), attr.getName(), AttributeExt.getDbType(attr)));
            }
        }
    }

    @Override
    public boolean columnExists(String table, String column) throws SQLException {
        ResultSet resultSet = _database.executeQuery(String.format("select count(1) from information_schema.columns where table_name='%s' and table_schema='public' and column_name='%s'", table, column));
        if(resultSet.next()) {
            return resultSet.getInt(1) > 0;
        }
        return false;
    }

    @Override
    public void dropColumn(Attribute... attributes) throws SQLException {
        if(!tableExists(attributes[0].getEntityname())){
            return;
        }
        StringBuilder dropSql = new StringBuilder();
        for (Attribute attr : attributes)
        {
            if (this.columnExists(attr.getEntityname(), attr.getName()))
            {
                dropSql.append(String.format("ALTER TABLE %s DROP COLUMN %s",
                        getSchemaTableName(attr.getEntityname()), attr.getName()));
            }
        }
        if (dropSql.length() > 0)
        {
            _database.execute(dropSql.toString());
        }
    }

    @Override
    public void alterView(Entity entity, List<Attribute> attributes, List<RelationShip> relationShips) throws SQLException {
        if(!tableExists(entity.getName())){
            return;
        }
        dropView(entity);
        this.createView(entity, attributes, relationShips);
    }

    @Override
    public void createView(Entity entity, List<Attribute> attributes, List<RelationShip> relationShips) throws SQLException {
        if(!tableExists(entity.getName())){
            return;
        }
        StringBuilder createSql = new StringBuilder();
        createSql.append(String.format("CREATE OR REPLACE VIEW %sview AS ", getSchemaTableName(entity.getName())));
        createSql.append("\nSELECT ");

        List<String> attrNames = new ArrayList<>();
        for (Attribute item : attributes)
        {
            if(this.columnExists(entity.getName(), item.getName())) {
                attrNames.add(String.format("%s.%s", entity.getName(), item.getName()));
            }
        }

        for (RelationShip item : relationShips)
        {
            if (attributes.stream().anyMatch(n -> n.getAttributeid().equals(item.getReferencingattributeid())))
            {
                attrNames.add(String.format("%s.name AS %sname", item.getName(), item.getReferencingattributename()));
            }
        }

        createSql.append(String.join(",\n", attrNames));
        //tables
        createSql.append(String.format("\nFROM %s", getSchemaTableName(entity.getName())));
        for (RelationShip item : relationShips)
        {
            createSql.append(String.format("\nLEFT JOIN %s AS %s ON %s.%s=%s.%s",
                    item.getReferencedentityname(), item.getName()
                    , entity.getName(), item.getReferencingattributename()
                    , item.getName(), item.getReferencedattributename()));
        }
        _database.execute(createSql.toString());
    }

    @Override
    public void createTable(Entity entity, List<Attribute> defaultAttributes) throws SQLException {
        if (this.tableExists(entity.getName()))
        {
            return;
        }
        StringBuilder s = new StringBuilder();
        s.append(String.format("CREATE TABLE %s(", getSchemaTableName(entity.getName())));
        for (Attribute attribute : defaultAttributes)
        {
            s.append(String.format("%s %s %s NULL,",
                    attribute.getName(), AttributeExt.getDbType(attribute), attribute.getIsnullable() ? "" : "NOT"));
        }
        List<String> idxColumns = new LinkedList<>();//需要创建索引的字段
        if (defaultAttributes.stream().filter(n -> n.getName().equals("statecode")).count() > 0)
        {
            idxColumns.add("statecode");
        }
        if (defaultAttributes.stream().filter(n -> n.getName().equals("createdon")).count() > 0)
        {
            idxColumns.add("createdon");
        }
        if (entity.getEntitymask() == EntityMask.User.value())
        {
            if (defaultAttributes.stream().filter(n -> n.getName().equals("ownerid")).count() > 0)
            {
                if (defaultAttributes.stream().filter(n -> n.getName().equals("owneridtype")).count() == 0)
                {
                    s.append("owneridtype int NOT NULL,");
                }
                idxColumns.add("ownerid");
            }
        }
        if (defaultAttributes.stream().filter(n -> n.getName().equals("organizationid")).count() > 0)
        {
            idxColumns.add("organizationid");
        }
        //主键
        s.append(String.format("CONSTRAINT pk_%s PRIMARY KEY (%sid)", entity.getName(), entity.getName()));
        //创建外键约束
        if (defaultAttributes.stream().filter(n -> n.getName().equals("createdby")).count() > 0)
        {
            s.append(",");
            s.append(String.format(" CONSTRAINT fk_%s_systemuser_createdby FOREIGN KEY(createdby)", entity.getName()));
            s.append(" REFERENCES systemuser (systemuserid) MATCH SIMPLE");
            s.append(" ON UPDATE RESTRICT ON DELETE RESTRICT");
        }
        if (entity.getEntitymask() == EntityMask.User.value())
        {
            if (defaultAttributes.stream().filter(n -> n.getName().equals("owningbusinessunit")).count() > 0)
            {
                s.append(",");
                s.append(String.format(" CONSTRAINT fk_%s_businessunit_owningbusinessunit FOREIGN KEY(owningbusinessunit)", entity.getName()));
                s.append(" REFERENCES businessunit (businessunitid) MATCH SIMPLE");
                s.append(" ON UPDATE RESTRICT ON DELETE RESTRICT");
            }
        }
        if (defaultAttributes.stream().filter(n -> n.getName().equals("organizationid")).count() > 0)
        {
            s.append(",");
            s.append(String.format(" CONSTRAINT fk_%s_organization_organizationid FOREIGN KEY(organizationid)", entity.getName()));
            s.append(" REFERENCES organization (organizationid) MATCH SIMPLE");
            s.append(" ON UPDATE RESTRICT ON DELETE RESTRICT");
        }

        //启动流程时建立约束
        if (entity.getWorkflowenabled())
        {
            if (defaultAttributes.stream().filter(n -> n.getName().equals("workflowid")).count() > 0)
            {
                s.append(",");
                s.append(String.format(" CONSTRAINT fk_%s_workflow_workflowid FOREIGN KEY(workflowid)", entity.getName()));
                s.append(" REFERENCES workflow (workflowid) MATCH SIMPLE");
                s.append(" ON UPDATE RESTRICT ON DELETE RESTRICT");
            }
        }
        s.append(")");
        _database.execute(s.toString());
    }

    @Override
    public boolean tableExists(String table) throws SQLException {
        ResultSet resultSet = _database.executeQuery(String.format("SELECT count(1) FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name='%s'", table));
        if(resultSet.next()) {
            return resultSet.getInt(1) > 0;
        }
        return false;
    }

    @Override
    public boolean viewExists(String name) throws SQLException {
        ResultSet resultSet = _database.executeQuery(String.format("SELECT count(1) FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW' AND table_name='%s'", name));
        if(resultSet.next()) {
            return resultSet.getInt(1) > 0;
        }
        return false;
    }

    @Override
    public void dropTable(Entity entity) throws SQLException {
        if (!this.tableExists(entity.getName()))
        {
            return;
        }
        StringBuilder dropSql = new StringBuilder();
        dropSql.append(String.format("DROP TABLE %s", getSchemaTableName(entity.getName())));
        _database.execute(dropSql.toString());
    }

    @Override
    public void dropView(Entity entity) throws SQLException {
        this.dropView(entity.getName() + "view");
    }

    @Override
    public void dropView(String name) throws SQLException {
        if (!this.viewExists(name))
        {
            return;
        }
        StringBuilder dropSql = new StringBuilder();
        dropSql.append(String.format("DROP VIEW %s", getSchemaTableName(name)));
        _database.execute(dropSql.toString());
    }

    private String getSchemaTableName(String name){
        switch(DataBaseSetting.dataBaseType){
            case PGSQL:
                return "public." + name;
            case SQLSERVER:
                return "dbo." + name;
            case MYSQL:
                return name;
            case ORACLE:
                return name;
            default:
                return name;
        }
    }
}
